package jrain.base.code;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.filter.logging.Slf4jLogFilter;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

import jrain.fw.core.dao.handler.IResultSetHandler;
import jrain.fw.dao.common.handler.RowMapListHandler;
import jrain.fw.id.snowflake.IdWorker;
import jrain.utils.exception.ServiceException;
import jrain.utils.lang.StringUtils;
import jrain.utils.prop.PropUtils;
import jrain.utils.sql.SqlUtils;

public class SchemaUtilsBase {

	private static IdWorker idWorker = new IdWorker(1);
	private static DruidDataSource druidDataSource;

	public static void main(String[] args) throws IOException {
		String suiteCode = "base";
		String table_name="'base_busi_paramd','base_busi_rs'"; //多个使用，号分隔。
		FileOutputStream fout = new FileOutputStream(new File("C:\\Users\\Administrator\\Desktop\\"+table_name+"sql.sql"));
		OutputStreamWriter fw = new OutputStreamWriter(fout, "UTF-8");
		String sql = "select table_name,column_name,data_type,column_default,column_comment,";
		sql += "CHARACTER_MAXIMUM_LENGTH length,NUMERIC_PRECISION nlength,NUMERIC_SCALE scale ";
		sql += "from information_schema.columns ";
		sql += "where table_schema='base'  and table_name in("+table_name+") ";
		List<Map<String, Object>> list = listBySql(sql);
		// 表分组
		Map<String, List<Map<String, Object>>> rsMap = new HashMap<>();
		for (Map<String, Object> map : list) {
			String tableName = StringUtils.trimNull(map.get("tableName"));
			List<Map<String, Object>> tempList = rsMap.get(tableName);
			if (tempList == null) {
				tempList = new ArrayList<>();
				rsMap.put(tableName, tempList);
			}
			tempList.add(map);
		}
		// 循环表
		Set<String> keySet = rsMap.keySet();
		for (String tableCode : keySet) {
			String serviceCode = StringUtils.tableToClass(tableCode) + "Business";
			List<Map<String, Object>> columns = rsMap.get(tableCode);
			// 生产列信息
			String columnsStrAll = "";
			String columnsStrUpdate = "";
			for (int index = 0; index < columns.size(); index++) {
				Map<String, Object> columnMap = columns.get(index);
				String columnCode = StringUtils.trimNull(columnMap.get("columnName"));
				String fieldCode = StringUtils.columnToField(columnCode);
				if (index != list.size() - 1) {
					columnsStrAll += columnCode + ",";
				} else {
					columnsStrAll += columnCode;
				}
				if (!"id".equals(fieldCode) && !"createTime".equals(fieldCode) && !"createUser".equals(fieldCode)) {
					if (index != list.size() - 1) {
						columnsStrUpdate += columnCode + ",";
					} else {
						columnsStrUpdate += columnCode;
					}
				}
			}
			if (columnsStrAll.endsWith(",")) {
				columnsStrAll = columnsStrAll.substring(0, columnsStrAll.lastIndexOf(','));
			}
			if (columnsStrUpdate.endsWith(",")) {
				columnsStrUpdate = columnsStrUpdate.substring(0, columnsStrUpdate.lastIndexOf(','));
			}
			// ADD
			// =========================================================================================================================
			StringBuffer busiAddSb = new StringBuffer();
			long busiAddId = idWorker.nextId();
			// Remove SQL
			String busiAddService = "" + serviceCode + ".add.v1";
			busiAddSb.append("delete from base_busi where suite_code = '" + suiteCode + "'   ");  // and app_code='t' and tenant_code='t'
			busiAddSb.append("and busi_key='" + busiAddService + "';\n");
			// add SQL
			busiAddSb.append("insert base_busi(");
			// app_code, 在 suite_code  之后 
			busiAddSb.append("id,suite_code,busi_code,busi_method,busi_version,busi_name,busi_key");
			busiAddSb.append(")values(");
			busiAddSb.append(busiAddId);
			busiAddSb.append(",");
			// ENV
			busiAddSb.append("'");
			busiAddSb.append(suiteCode);
			busiAddSb.append("',");
			// APP_CODE
			// busiAddSb.append("'t'");
			// busiAddSb.append(",");
			// TALENT_CODE
			//busiAddSb.append("'t'");
			//busiAddSb.append(",");
			// BUSI SERVICE
			busiAddSb.append("'" + serviceCode + "'");
			busiAddSb.append(",");
			// BUSI METHOD
			busiAddSb.append("'add'");
			busiAddSb.append(",");
			// BUSI version
			busiAddSb.append("'v1'");
			busiAddSb.append(",");
			// BUSI NAME
			busiAddSb.append("'" + busiAddService + "'");
			busiAddSb.append(",");
			// BUSI KEY
			busiAddSb.append("'" + busiAddService + "'");
			busiAddSb.append(");");
			fw.write(busiAddSb.toString());
			fw.write("\n");
			// =========================================================================
			fw.write("delete from base_busi_param  where suite_code = '" + suiteCode + "'  ");  //and app_code='t' // and tenant_code='t'
			fw.write("and busi_key='" + busiAddService + "';\n");
			for (int index = 0; index < columns.size(); index++) {
				StringBuffer busiParamSb = new StringBuffer();
				Map<String, Object> columnMap = columns.get(index);
				String columnCode = StringUtils.trimNull(columnMap.get("columnName"));
				String fieldCode = StringUtils.columnToField(columnCode);
				String dataType = StringUtils.trimNull(columnMap.get("dataType"));
				String length = StringUtils.trimNull(columnMap.get("length"));
				String nlength = StringUtils.trimNull(columnMap.get("nlength"));
				String scale = StringUtils.trimNull(columnMap.get("scale"));
				String columnDefault = StringUtils.trimNull(columnMap.get("columnDefault"));
				String columnComment = StringUtils.trimNull(columnMap.get("columnComment"));
				busiParamSb.append("insert base_busi_param(");
				busiParamSb.append("id,suite_code,busi_id,busi_key,param_key,param_name,param_type,");
				busiParamSb.append("param_length,param_scale,param_empty,param_dvalue,param_range");
				busiParamSb.append(")values(");
				busiParamSb.append(idWorker.nextId());
				busiParamSb.append(",");
				// ENV
				busiParamSb.append("'");
				busiParamSb.append(suiteCode);
				busiParamSb.append("',");
				// APP_CODE
				// busiParamSb.append("'t'");
				// busiParamSb.append(",");
				// TALENT_CODE
				// busiParamSb.append("'t'");
				// busiParamSb.append(",");
				// busi_id
				busiParamSb.append(busiAddId);
				busiParamSb.append(",");
				// BUIS_KEY
				busiParamSb.append("'" + busiAddService + "'");
				busiParamSb.append(",");
				// param_key
				busiParamSb.append("'" + fieldCode + "'");
				busiParamSb.append(",");
				// param_name
				if (columnComment.length() > 0) {
					busiParamSb.append("'" + columnComment + "'");
					busiParamSb.append(",");
				} else {
					busiParamSb.append("'" + fieldCode + "'");
					busiParamSb.append(",");
				}
				// param_type
				if (dataType.equals("int")) {
					busiParamSb.append("'int'");
					busiParamSb.append(",");
				} else if (dataType.equals("bigint")) {
					busiParamSb.append("'long'");
					busiParamSb.append(",");
				} else if (dataType.equals("datetime")) {
					busiParamSb.append("'date'");
					busiParamSb.append(",");
				} else if (dataType.equals("decimal")) {
					busiParamSb.append("'money'");
					busiParamSb.append(",");
				} else {
					busiParamSb.append("'string'");
					busiParamSb.append(",");
				}
				// param_length
				if (dataType.equals("int")) {
					busiParamSb.append("0");
					busiParamSb.append(",");
				} else if (dataType.equals("bigint")) {
					busiParamSb.append("0");
					busiParamSb.append(",");
				} else if (dataType.equals("datetime")) {
					busiParamSb.append("0");
					busiParamSb.append(",");
				} else if (dataType.equals("decimal")) {
					busiParamSb.append(nlength);
					busiParamSb.append(",");
				} else {
					busiParamSb.append(length);
					busiParamSb.append(",");
				}
				// param_scale
				if (dataType.equals("decimal")) {
					busiParamSb.append(scale);
				} else {
					busiParamSb.append('0');
				}
				// param_empty
				busiParamSb.append(",");
				busiParamSb.append("'Y'");
				// param_davlue
				busiParamSb.append(",");
				if ("createTime".equals(fieldCode) || "lastTime".equals(fieldCode)) {
					busiParamSb.append("'d_time'");
				} else if ("createUser".equals(fieldCode) || "lastUser".equals(fieldCode)) {
					busiParamSb.append("'d_user'");
				} else if ("rowSort".equals(fieldCode)) {
					busiParamSb.append("'1000'");
				} else if ("status".equals(fieldCode)) {
					busiParamSb.append("'1'");
				} else if ("id".equals(fieldCode)) {
					busiParamSb.append("''");
				} else {
					if (dataType.equals("int") || dataType.equals("bigint")) {
						if (columnDefault.length() > 0) {
							busiParamSb.append("'");
							busiParamSb.append(columnDefault);
							busiParamSb.append("'");
						} else {
							busiParamSb.append("'0'");
						}
					} else if (dataType.equals("string")) {
						if (columnDefault.length() > 0) {
							busiParamSb.append("'");
							busiParamSb.append(columnDefault);
							busiParamSb.append("'");
						} else {
							busiParamSb.append("''");
						}
					} else {
						busiParamSb.append("''");
					}
				}
				// param_range
				busiParamSb.append(",");
				if ("status".equals(fieldCode)) {
					busiParamSb.append("'1,2'");
				} else {
					busiParamSb.append("''");
				}
				busiParamSb.append(");");
				fw.write(busiParamSb.toString());
				fw.write("\n");
			}
			// sql=================================================================
			fw.write("delete from base_busi_sql  where suite_code = '" + suiteCode + "'     "); //and app_code='t' and tenant_code='t'
			fw.write("and is_base=1 and busi_key='" + busiAddService + "';\n");
			StringBuffer addSql = new StringBuffer();
			addSql.append("insert base_busi_sql(");
			addSql.append("id,suite_code,busi_id,busi_key,sql_code,sql_name,");
			addSql.append("sql_type,sql_value,sql_table,sql_columns,sql_condition");
			addSql.append(")values(");
			// ID
			addSql.append(idWorker.nextId());
			addSql.append(",");
			// ENV
			addSql.append("'");
			addSql.append(suiteCode);
			addSql.append("',");
			// APP_CODE
			// addSql.append("'t'");
			// addSql.append(",");
			// TALENT_CODE
			// addSql.append("'t'");
			// addSql.append(",");
			// BUSI_ID
			addSql.append(busiAddId);
			addSql.append(",");
			// BUIS_KEY
			addSql.append("'" + busiAddService + "'");
			addSql.append(",");
			// SQL_CODE
			addSql.append("'default'");
			addSql.append(",");
			// SQL_NAME
			addSql.append("'default'");
			addSql.append(",");
			// SQL_TYPE
			addSql.append("'insert'");
			addSql.append(",");
			// SQL_VALUE
			addSql.append("'");
			addSql.append("");
			addSql.append("',");
			// SQL_TABLE
			addSql.append("'");
			addSql.append(tableCode);
			addSql.append("',");
			// SQL_COLUMNS
			addSql.append("'");
			addSql.append(columnsStrAll);
			addSql.append("',");
			// SQL_CONDITION
			addSql.append("'");
			addSql.append(tableCode);
			addSql.append("'");
			addSql.append(");");
			fw.write(addSql.toString());
			fw.write("\n");
			// MODIFY=========================================================================================================================
			StringBuffer busiModifySb = new StringBuffer();
			long busiModifyId = idWorker.nextId();
			String busiModifyService = "" + serviceCode + ".modify.v1";
			// Remove SQL
			busiModifySb.append("delete from base_busi where suite_code = '" + suiteCode + "'   "); // and app_code='t' and tenant_code='t'
			busiModifySb.append("and busi_key='" + busiModifyService + "';\n");
			// add SQL
			busiModifySb.append("insert base_busi(");
			busiModifySb.append("id,suite_code,busi_code,busi_method,busi_version,busi_name,busi_key");
			busiModifySb.append(")values(");
			// ID
			busiModifySb.append(busiModifyId);
			busiModifySb.append(",");
			// ENV
			busiModifySb.append("'");
			busiModifySb.append(suiteCode);
			busiModifySb.append("',");
			// APP_CODE
			//busiModifySb.append("'t'");
			//busiModifySb.append(",");
			// TALENT_CODE
			//busiModifySb.append("'t'");
			//busiModifySb.append(",");
			// BUSI SERVICE
			busiModifySb.append("'" + serviceCode + "'");
			busiModifySb.append(",");
			// BUSI METHOD
			busiModifySb.append("'modify'");
			busiModifySb.append(",");
			// BUSI version
			busiModifySb.append("'v1'");
			busiModifySb.append(",");
			// BUSI NAME
			busiModifySb.append("'" + busiModifyService + "'");
			busiModifySb.append(",");
			// BUSI KEY
			busiModifySb.append("'" + busiModifyService + "'");
			busiModifySb.append(");");
			fw.write(busiModifySb.toString());
			fw.write("\n");
			fw.write("delete from base_busi_param  where suite_code = '" + suiteCode + "'    "); //and app_code='t' and tenant_code='t'
			fw.write("and busi_key='" + busiModifyService + "';\n");
			// =================================================================
			for (int index = 0; index < columns.size(); index++) {
				StringBuffer busiParamSb = new StringBuffer();
				Map<String, Object> columnMap = columns.get(index);
				String columnCode = StringUtils.trimNull(columnMap.get("columnName"));
				String fieldCode = StringUtils.columnToField(columnCode);
				String dataType = StringUtils.trimNull(columnMap.get("dataType"));
				String length = StringUtils.trimNull(columnMap.get("length"));
				String nlength = StringUtils.trimNull(columnMap.get("nlength"));
				String scale = StringUtils.trimNull(columnMap.get("scale"));
				String columnDefault = StringUtils.trimNull(columnMap.get("columnDefault"));
				String columnComment = StringUtils.trimNull(columnMap.get("columnComment"));
				busiParamSb.append("insert base_busi_param(");
				busiParamSb.append("id,suite_code,busi_id,busi_key,param_key,param_name,param_type,");
				busiParamSb.append("param_length,param_scale,param_empty,param_dvalue,param_range");
				busiParamSb.append(")values(");
				busiParamSb.append(idWorker.nextId());
				busiParamSb.append(",");
				// ENV
				busiParamSb.append("'");
				busiParamSb.append(suiteCode);
				busiParamSb.append("',");
				// APP_CODE
				//busiParamSb.append("'t'");
				//busiParamSb.append(",");
				// TALENT_CODE
				//busiParamSb.append("'t'");
				//busiParamSb.append(",");
				// busi_id
				busiParamSb.append(busiModifyId);
				busiParamSb.append(",");
				// BUIS_KEY
				busiParamSb.append("'" + busiModifyService + "'");
				busiParamSb.append(",");
				// param_key
				busiParamSb.append("'" + fieldCode + "'");
				busiParamSb.append(",");
				// param_name
				if (columnComment.length() > 0) {
					busiParamSb.append("'" + columnComment + "'");
					busiParamSb.append(",");
				} else {
					busiParamSb.append("'" + fieldCode + "'");
					busiParamSb.append(",");
				}
				// param_type
				if (dataType.equals("int")) {
					busiParamSb.append("'int'");
					busiParamSb.append(",");
				} else if (dataType.equals("bigint")) {
					busiParamSb.append("'long'");
					busiParamSb.append(",");
				} else if (dataType.equals("datetime")) {
					busiParamSb.append("'date'");
					busiParamSb.append(",");
				} else if (dataType.equals("decimal")) {
					busiParamSb.append("'money'");
					busiParamSb.append(",");
				} else {
					busiParamSb.append("'string'");
					busiParamSb.append(",");
				}
				// param_length
				if (dataType.equals("int")) {
					busiParamSb.append("0");
					busiParamSb.append(",");
				} else if (dataType.equals("bigint")) {
					busiParamSb.append("0");
					busiParamSb.append(",");
				} else if (dataType.equals("datetime")) {
					busiParamSb.append("0");
					busiParamSb.append(",");
				} else if (dataType.equals("decimal")) {
					busiParamSb.append(nlength);
					busiParamSb.append(",");
				} else {
					busiParamSb.append(length);
					busiParamSb.append(",");
				}
				// param_scale
				if (dataType.equals("decimal")) {
					busiParamSb.append(scale);
				} else {
					busiParamSb.append('0');
				}
				// param_empty
				busiParamSb.append(",");
				busiParamSb.append("'Y'");
				// param_davlue
				busiParamSb.append(",");
				if ("createTime".equals(fieldCode) || "lastTime".equals(fieldCode)) {
					busiParamSb.append("'d_time'");
				} else if ("createUser".equals(fieldCode) || "lastUser".equals(fieldCode)) {
					busiParamSb.append("'d_user'");
				} else if ("rowSort".equals(fieldCode)) {
					busiParamSb.append("'1000'");
				} else if ("status".equals(fieldCode)) {
					busiParamSb.append("'1'");
				} else if ("id".equals(fieldCode)) {
					busiParamSb.append("''");
				} else {
					if (dataType.equals("int") || dataType.equals("bigint")) {
						if (columnDefault.length() > 0) {
							busiParamSb.append("'");
							busiParamSb.append(columnDefault);
							busiParamSb.append("'");
						} else {
							busiParamSb.append("'0'");
						}
					} else if (dataType.equals("string")) {
						if (columnDefault.length() > 0) {
							busiParamSb.append("'");
							busiParamSb.append(columnDefault);
							busiParamSb.append("'");
						} else {
							busiParamSb.append("''");
						}
					} else {
						busiParamSb.append("''");
					}
				}
				// param_range
				busiParamSb.append(",");
				if ("status".equals(fieldCode)) {
					busiParamSb.append("'1,2'");
				} else {
					busiParamSb.append("''");
				}
				busiParamSb.append(");");
				fw.write(busiParamSb.toString());
				fw.write("\n");
			}
			// =================================================================
			fw.write("delete from base_busi_sql  where suite_code = '" + suiteCode + "'    "); //and app_code='t' and tenant_code='t'
			fw.write("and is_base=1 and busi_key='" + busiModifyService + "';\n");
			// modifySql
			StringBuffer modifySql = new StringBuffer();
			modifySql.append("insert base_busi_sql(");
			modifySql.append("id,suite_code,busi_id,busi_key,sql_code,sql_name,");
			modifySql.append("sql_type,sql_value,sql_table,sql_columns,sql_condition");
			modifySql.append(")values(");
			// ID
			modifySql.append(idWorker.nextId());
			modifySql.append(",");
			// ENV
			modifySql.append("'");
			modifySql.append(suiteCode);
			modifySql.append("',");
			// APP_CODE
			//modifySql.append("'t'");
			//modifySql.append(",");
			// TALENT_CODE
			//modifySql.append("'t'");
			//modifySql.append(",");
			// BUSI_ID
			modifySql.append(busiModifyId);
			modifySql.append(",");
			// BUIS_KEY
			modifySql.append("'" + busiModifyService + "'");
			modifySql.append(",");
			// SQL_CODE
			modifySql.append("'default'");
			modifySql.append(",");
			// SQL_NAME
			modifySql.append("'default'");
			modifySql.append(",");
			// SQL_TYPE
			modifySql.append("'update'");
			modifySql.append(",");
			// SQL_VALUE
			modifySql.append("'");
			modifySql.append("update " + tableCode + " set #{columns} where 1=1 #{condition}");
			modifySql.append("',");
			// SQL_TABLE
			modifySql.append("'");
			modifySql.append(tableCode);
			modifySql.append("',");
			// SQL_COLUMNS
			modifySql.append("'");
			modifySql.append(columnsStrUpdate);
			modifySql.append("',");
			// SQL_CONDITION
			modifySql.append("'");
			modifySql.append(columnsStrAll);
			modifySql.append("'");
			modifySql.append(");");
			fw.write(modifySql.toString());
			fw.write("\n");
			// REMVOE=========================================================================================================================
			StringBuffer busiRemvoeSb = new StringBuffer();
			long busiRemoveId = idWorker.nextId();
			String busiRemoveService = "" + serviceCode + ".remove.v1";
			// Remove SQL
			busiRemvoeSb.append("delete from base_busi where suite_code = '" + suiteCode + "'   ");//and app_code='t' and tenant_code='t' 
			busiRemvoeSb.append("and busi_key='" + busiRemoveService + "';\n");
			// add SQL
			busiRemvoeSb.append("insert base_busi(");
			busiRemvoeSb.append("id,suite_code,busi_code,busi_method,busi_version,busi_name,busi_key");
			busiRemvoeSb.append(")values(");
			// ID
			busiRemvoeSb.append(busiRemoveId);
			busiRemvoeSb.append(",");
			// ENV
			busiRemvoeSb.append("'");
			busiRemvoeSb.append(suiteCode);
			busiRemvoeSb.append("',");
			// APP_CODE
			//busiRemvoeSb.append("'t'");
			//busiRemvoeSb.append(",");
			// TALENT_CODE
			//busiRemvoeSb.append("'t'");
			//busiRemvoeSb.append(",");
			// BUSI SERVICE
			busiRemvoeSb.append("'" + serviceCode + "'");
			busiRemvoeSb.append(",");
			// BUSI METHOD
			busiRemvoeSb.append("'remove'");
			busiRemvoeSb.append(",");
			// BUSI version
			busiRemvoeSb.append("'v1'");
			busiRemvoeSb.append(",");
			// BUSI NAME
			busiRemvoeSb.append("'" + busiRemoveService + "'");
			busiRemvoeSb.append(",");
			// BUSI KEY
			busiRemvoeSb.append("'" + busiRemoveService + "'");
			busiRemvoeSb.append(");");
			fw.write(busiRemvoeSb.toString());
			fw.write("\n");
			fw.write("delete from base_busi_param  where suite_code = '" + suiteCode + "'   ");//and app_code='t' and tenant_code='t' 
			fw.write("and busi_key='" + busiRemoveService + "';\n");
			// =================================================================
			StringBuffer busiParamSb = new StringBuffer();
			busiParamSb.append("insert base_busi_param(");
			busiParamSb.append("id,suite_code,busi_id,busi_key,param_key,param_name,param_type,");
			busiParamSb.append("param_length,param_scale,param_empty,param_dvalue,param_range");
			busiParamSb.append(")values(");
			busiParamSb.append(idWorker.nextId());
			busiParamSb.append(",");
			// ENV
			busiParamSb.append("'");
			busiParamSb.append(suiteCode);
			busiParamSb.append("',");
			// APP_CODE
			//busiParamSb.append("'t'");
			//busiParamSb.append(",");
			// TALENT_CODE
			busiParamSb.append("'t'");
			busiParamSb.append(",");
			// busi_id
			busiParamSb.append(busiRemoveId);
			busiParamSb.append(",");
			// BUIS_KEY
			busiParamSb.append("'" + busiRemoveService + "'");
			busiParamSb.append(",");
			// param_key
			busiParamSb.append("'id'");
			busiParamSb.append(",");
			// param_name
			busiParamSb.append("'ID'");
			busiParamSb.append(",");
			// param_type
			busiParamSb.append("'string'");
			busiParamSb.append(",");
			// param_length
			busiParamSb.append("0");
			busiParamSb.append(",");
			// param_scale
			busiParamSb.append('0');
			busiParamSb.append(",");
			// param_empty
			busiParamSb.append("''");
			busiParamSb.append(",");
			// param_davlue
			busiParamSb.append("''");
			busiParamSb.append(",");
			// param_range
			busiParamSb.append("''");
			busiParamSb.append(");");
			fw.write(busiParamSb.toString());
			fw.write("\n");
			// =================================================================
			fw.write("delete from base_busi_sql  where suite_code = '" + suiteCode + "'    ");//and app_code='t' and tenant_code='t'
			fw.write("and is_base=1 and busi_key='" + busiRemoveService + "';\n");
			// removeSql
			StringBuffer removeSql = new StringBuffer();
			removeSql.append("insert base_busi_sql(");
			removeSql.append("id,suite_code,busi_id,busi_key,sql_code,sql_name,");
			removeSql.append("sql_type,sql_value,sql_table,sql_columns,sql_condition");
			removeSql.append(")values(");
			// ID
			removeSql.append(idWorker.nextId());
			removeSql.append(",");
			// ENV
			removeSql.append("'");
			removeSql.append(suiteCode);
			removeSql.append("',");
			// APP_CODE
			//removeSql.append("'t'");
			//removeSql.append(",");
			// TALENT_CODE
			//removeSql.append("'t'");
			//removeSql.append(",");
			// BUSI_ID
			removeSql.append(busiRemoveId);
			removeSql.append(",");
			// BUIS_KEY
			removeSql.append("'" + busiRemoveService + "'");
			removeSql.append(",");
			// SQL_CODE
			removeSql.append("'default'");
			removeSql.append(",");
			// SQL_NAME
			removeSql.append("'default'");
			removeSql.append(",");
			// SQL_TYPE
			removeSql.append("'delete'");
			removeSql.append(",");
			// SQL_VALUE
			removeSql.append("'");
			removeSql.append("delete from " + tableCode + " where 1=1 #{condition}");
			removeSql.append("',");
			// SQL_TABLE
			removeSql.append("'");
			removeSql.append(tableCode);
			removeSql.append("',");
			// SQL_COLUMNS
			removeSql.append("'");
			removeSql.append("");
			removeSql.append("',");
			// SQL_CONDITION
			removeSql.append("'");
			removeSql.append(columnsStrAll);
			removeSql.append("'");
			removeSql.append(");");
			fw.write(removeSql.toString());
			fw.write("\n");
			// Query=========================================================================================================================
			StringBuffer busiQuerySb = new StringBuffer();
			long busiQueryId = idWorker.nextId();
			String busiQueryService = "" + serviceCode + ".query.v1";
			// Remove SQL
			busiQuerySb.append("delete from base_busi where suite_code = '" + suiteCode + "'    ");//and app_code='t' and tenant_code='t'
			busiQuerySb.append("and busi_key='" + busiQueryService + "';\n");
			// add SQL
			busiQuerySb.append("insert base_busi(");
			busiQuerySb.append("id,suite_code,busi_code,busi_method,busi_version,busi_name,busi_key");
			busiQuerySb.append(")values(");
			// ID
			busiQuerySb.append(busiQueryId);
			busiQuerySb.append(",");
			// ENV
			busiQuerySb.append("'");
			busiQuerySb.append(suiteCode);
			busiQuerySb.append("',");
			// APP_CODE
			//busiQuerySb.append("'t'");
			//busiQuerySb.append(",");
			// TALENT_CODE
			busiQuerySb.append("'t'");
			busiQuerySb.append(",");
			// BUSI SERVICE
			busiQuerySb.append("'" + serviceCode + "'");
			busiQuerySb.append(",");
			// BUSI METHOD
			busiQuerySb.append("'query'");
			busiQuerySb.append(",");
			// BUSI version
			busiQuerySb.append("'v1'");
			busiQuerySb.append(",");
			// BUSI NAME
			busiQuerySb.append("'" + busiQueryService + "'");
			busiQuerySb.append(",");
			// BUSI KEY
			busiQuerySb.append("'" + busiQueryService + "'");
			busiQuerySb.append(");");
			fw.write(busiQuerySb.toString());
			fw.write("\n");

			// =================================================================
			fw.write("delete from base_busi_sql  where suite_code = '" + suiteCode + "'  and app_code='t' and tenant_code='t'  ");
			fw.write("and is_base=1  and busi_key='" + busiQueryService + "';\n");
			// querySql
			StringBuffer querySql = new StringBuffer();
			querySql.append("insert base_busi_sql(");
			querySql.append("id,suite_code,app_code,tenant_code,busi_id,busi_key,sql_code,sql_name,");
			querySql.append("sql_type,sql_value,sql_table,sql_columns,sql_condition");
			querySql.append(")values(");
			// ID
			querySql.append(idWorker.nextId());
			querySql.append(",");
			// ENV
			querySql.append("'");
			querySql.append(suiteCode);
			querySql.append("',");
			// APP_CODE
			querySql.append("'t'");
			querySql.append(",");
			// TALENT_CODE
			querySql.append("'t'");
			querySql.append(",");
			// BUSI SERVICE
			// BUSI_ID
			querySql.append(busiQueryId);
			querySql.append(",");
			// BUIS_KEY
			querySql.append("'" + busiQueryService + "'");
			querySql.append(",");
			// SQL_CODE
			querySql.append("'default'");
			querySql.append(",");
			// SQL_NAME
			querySql.append("'default'");
			querySql.append(",");
			// SQL_TYPE
			querySql.append("'select'");
			querySql.append(",");
			// SQL_VALUE
			querySql.append("'");
			querySql.append("select #{columns} from " + tableCode + " where 1=1 #{condition}");
			querySql.append("',");
			// SQL_TABLE
			querySql.append("'");
			querySql.append(tableCode);
			querySql.append("',");
			// SQL_COLUMNS
			querySql.append("'");
			querySql.append(columnsStrAll);
			querySql.append("',");
			// SQL_CONDITION
			querySql.append("'");
			querySql.append(columnsStrAll);
			querySql.append("'");
			querySql.append(");");
			fw.write(querySql.toString());
			fw.write("\n");
			// COUNT=========================================================================================================================
			StringBuffer busiCountSb = new StringBuffer();
			long busiCountId = idWorker.nextId();
			String busiCountService = "" + serviceCode + ".count.v1";
			// Remove SQL
			busiCountSb.append("delete from base_busi where suite_code = '" + suiteCode + "'  and app_code='t' and tenant_code='t'  ");
			busiCountSb.append("and busi_key='" + busiCountService + "';\n");
			// add SQL
			busiCountSb.append("insert base_busi(");
			busiCountSb.append("id,suite_code,app_code,tenant_code,busi_code,busi_method,busi_version,busi_name,busi_key");
			busiCountSb.append(")values(");
			// ID
			busiCountSb.append(busiCountId);
			busiCountSb.append(",");
			// ENV
			busiCountSb.append("'");
			busiCountSb.append(suiteCode);
			busiCountSb.append("',");
			// APP_CODE
			busiCountSb.append("'t'");
			busiCountSb.append(",");
			// TALENT_CODE
			busiCountSb.append("'t'");
			busiCountSb.append(",");
			// BUSI SERVICE
			busiCountSb.append("'" + serviceCode + "'");
			busiCountSb.append(",");
			// BUSI METHOD
			busiCountSb.append("'count'");
			busiCountSb.append(",");
			// BUSI version
			busiCountSb.append("'v1'");
			busiCountSb.append(",");
			// BUSI NAME
			busiCountSb.append("'" + busiCountService + "'");
			busiCountSb.append(",");
			// BUSI KEY
			busiCountSb.append("'" + busiCountService + "'");
			busiCountSb.append(");");
			fw.write(busiCountSb.toString());
			fw.write("\n");
			// =================================================================
			fw.write("delete from base_busi_sql  where suite_code = '" + suiteCode + "' ");
			fw.write("and is_base=1 and busi_key='" + busiCountService + "';\n");
			// querySql
			StringBuffer countSql = new StringBuffer();
			countSql.append("insert base_busi_sql(");
			countSql.append("id,suite_code,app_code,tenant_code,busi_id,busi_key,sql_code,sql_name,");
			countSql.append("sql_type,sql_value,sql_table,sql_columns,sql_condition");
			countSql.append(")values(");
			// ID
			countSql.append(idWorker.nextId());
			countSql.append(",");
			// ENV
			countSql.append("'");
			countSql.append(suiteCode);
			countSql.append("',");
			// APP_CODE
			countSql.append("'t'");
			countSql.append(",");
			// TALENT_CODE
			countSql.append("'t'");
			countSql.append(",");
			// BUSI_ID
			countSql.append(busiCountId);
			countSql.append(",");
			// BUIS_KEY
			countSql.append("'" + busiCountService + "'");
			countSql.append(",");
			// SQL_CODE
			countSql.append("'default'");
			countSql.append(",");
			// SQL_NAME
			countSql.append("'default'");
			countSql.append(",");
			// SQL_TYPE
			countSql.append("'select'");
			countSql.append(",");
			// SQL_VALUE
			countSql.append("'");
			countSql.append("select count(1) count from " + tableCode + " where 1=1  #{condition} #{order}");
			countSql.append("',");
			// SQL_TABLE
			countSql.append("'");
			countSql.append(tableCode);
			countSql.append("',");
			// SQL_COLUMNS
			countSql.append("'");
			countSql.append("");
			countSql.append("',");
			// SQL_CONDITION
			countSql.append("'");
			countSql.append(columnsStrAll);
			countSql.append("'");
			countSql.append(");");
			fw.write(countSql.toString());
			fw.write("\n");
			// LIST=========================================================================================================================
			StringBuffer busiListSb = new StringBuffer();
			long busiListId = idWorker.nextId();
			String busiListService = "" + serviceCode + ".list.v1";
			// Remove SQL
			busiListSb.append("delete from base_busi where suite_code = '" + suiteCode + "'  and app_code='t' and tenant_code='t'  ");
			busiListSb.append("and busi_key='" + busiListService + "';\n");
			// add SQL
			busiListSb.append("insert base_busi(");
			busiListSb.append("id,suite_code,app_code,tenant_code,busi_code,busi_method,busi_version,busi_name,busi_key");
			busiListSb.append(")values(");
			// ID
			busiListSb.append(busiListId);
			busiListSb.append(",");
			// ENV
			busiListSb.append("'");
			busiListSb.append(suiteCode);
			busiListSb.append("',");
			// APP_CODE
			busiListSb.append("'t'");
			busiListSb.append(",");
			// TALENT_CODE
			busiListSb.append("'t'");
			busiListSb.append(",");
			// BUSI SERVICE
			busiListSb.append("'" + serviceCode + "'");
			busiListSb.append(",");
			// BUSI METHOD
			busiListSb.append("'list'");
			busiListSb.append(",");
			// BUSI version
			busiListSb.append("'v1'");
			busiListSb.append(",");
			// BUSI NAME
			busiListSb.append("'" + busiListService + "'");
			busiListSb.append(",");
			// BUSI KEY
			busiListSb.append("'" + busiListService + "'");
			busiListSb.append(");");
			fw.write(busiListSb.toString());
			fw.write("\n");
			// =================================================================
			fw.write("delete from base_busi_sql  where suite_code = '" + suiteCode + "'  and app_code='t' and tenant_code='t'  ");
			fw.write("and is_base=1 and busi_key='" + busiListService + "';\n");
			// listSql
			StringBuffer listSql = new StringBuffer();
			listSql.append("insert base_busi_sql(");
			listSql.append("id,suite_code,app_code,tenant_code,busi_id,busi_key,sql_code,sql_name,");
			listSql.append("sql_type,sql_value,sql_table,sql_columns,sql_condition");
			listSql.append(")values(");
			// ID
			listSql.append(idWorker.nextId());
			listSql.append(",");
			// ENV
			listSql.append("'");
			listSql.append(suiteCode);
			listSql.append("',");
			// APP_CODE
			listSql.append("'t'");
			listSql.append(",");
			// TALENT_CODE
			listSql.append("'t'");
			listSql.append(",");
			// BUSI_ID
			listSql.append(busiListId);
			listSql.append(",");
			// BUIS_KEY
			listSql.append("'" + busiListService + "'");
			listSql.append(",");
			// SQL_CODE
			listSql.append("'default'");
			listSql.append(",");
			// SQL_NAME
			listSql.append("'default'");
			listSql.append(",");
			// SQL_TYPE
			listSql.append("'select'");
			listSql.append(",");
			// SQL_VALUE
			listSql.append("'");
			listSql.append("select #{columns} from " + tableCode + " where 1=1   #{condition} #{order}");
			listSql.append("',");
			// SQL_TABLE
			listSql.append("'");
			listSql.append(tableCode);
			listSql.append("',");
			// SQL_COLUMNS
			listSql.append("'");
			listSql.append(columnsStrAll);
			listSql.append("',");
			// SQL_CONDITION
			listSql.append("'");
			listSql.append(columnsStrAll);
			listSql.append("'");
			listSql.append(");");
			fw.write(listSql.toString());
			fw.write("\n");
			fw.flush();
		}
		fw.close();
	}

	private static List<Map<String, Object>> listBySql(String sql, Object... params) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					pstmt.setObject(i + 1, params[i]);
				}
			}
			rs = pstmt.executeQuery();
			IResultSetHandler<List<Map<String, Object>>> handler = new RowMapListHandler();
			List<Map<String, Object>> list = handler.handle(rs, new HashMap<>());
			return list;
		} catch (Exception e) {
			throw new ServiceException(e.getMessage(), e);
		} finally {
			SqlUtils.closeRs(rs);
			rs = null;
			SqlUtils.closeStmt(pstmt);
			pstmt = null;
			SqlUtils.closeConn(conn);
			conn = null;
		}
	}

	private static Connection getConnection() throws SQLException {
		Map<String, Object> configMap = PropUtils.getClasspathConf("cfg.conf");
		druidDataSource = new DruidDataSource();
		List<Filter> filters = new ArrayList<>();
		Slf4jLogFilter filter = new Slf4jLogFilter();
		filter.setStatementExecutableSqlLogEnable(true);
		filters.add(filter);
		druidDataSource.setProxyFilters(filters);
		DruidDataSourceFactory.config(druidDataSource, configMap);
		return druidDataSource.getConnection();
	}

}
